import { query } from '../db'

export interface Project {
  id: number
  name: string
  description: string
  detail: string
  image: string
  demo_url?: string
  github_url?: string
  website_url?: string
  tags: string[]
  features: string[]
  tech_stack: string[]
  start_date: string
  end_date?: string
  status: 'planning' | 'in_progress' | 'completed' | 'maintenance' // 添加新的项目状态 
  priority: number
  featured: boolean
  views: number
  likes: number
  created_at: string
  updated_at: string
}

// 安全解析函数，处理 JSON 和逗号分隔的字符串
function safeParseArray(value: string | null): string[] {
  if (!value) return []
  // console.log('原始值:', value, typeof value)

  if(typeof value !== 'string') return value

  // 尝试解析 JSON
  try {
    const parsed = JSON.parse(value)
    return Array.isArray(parsed) ? parsed : []
  } catch (error) {
    // 如果 JSON 解析失败，按逗号分隔处理
    return value.split(',').map(item => item.trim()).filter(item => item.length > 0)
  }
}

// 获取所有项目
export async function getAllProjects(): Promise<Project[]> {
  const sql = `
    SELECT * FROM blog_projects 
    WHERE status != 'planning' 
    ORDER BY featured DESC, priority DESC, created_at DESC
  `
  const projects = await query(sql) as any[]
  
  return projects.map(project => ({
    ...project,
    tags: safeParseArray(project.tags),
    features: safeParseArray(project.features),
    tech_stack: safeParseArray(project.tech_stack)
  }))
}

// 获取推荐项目
export async function getFeaturedProjects(): Promise<Project[]> {
  const sql = `
    SELECT * FROM blog_projects 
    WHERE featured = 1 AND status != 'planning' 
    ORDER BY priority DESC, created_at DESC
  `
  const projects = await query(sql) as any[]
  
  return projects.map(project => ({
    ...project,
    tags: JSON.parse(project.tags || '[]'),
    features: JSON.parse(project.features || '[]'),
    tech_stack: JSON.parse(project.tech_stack || '[]')
  }))
}

// 根据ID获取项目
export async function getProjectById(id: number): Promise<Project | null> {
  const sql = 'SELECT * FROM blog_projects WHERE id = ?'
  const projects = await query(sql, [id]) as any[]
  
  if (projects.length === 0) return null
  
  const project = projects[0]
  return {
    ...project,
    tags: JSON.parse(project.tags || '[]'),
    features: JSON.parse(project.features || '[]'),
    tech_stack: JSON.parse(project.tech_stack || '[]')
  }
}

// 增加项目浏览次数
export async function incrementProjectViews(projectId: number): Promise<void> {
  const sql = 'UPDATE blog_projects SET views = views + 1 WHERE id = ?'
  await query(sql, [projectId])
}

// 增加项目点赞数
export async function incrementProjectLikes(projectId: number): Promise<void> {
  const sql = 'UPDATE blog_projects SET likes = likes + 1 WHERE id = ?'
  await query(sql, [projectId])
} 

// 添加项目
export async function addProject(project: Project): Promise<void> { 
  const sql = 'INSERT INTO blog_projects (name, description, detail, likes, image, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
  project.created_at = new Date().toISOString()
  project.updated_at = new Date().toISOString()
  await query(sql, [project.name, project.description, project.detail, project.likes, project.image, project.status, project.created_at, project.updated_at])
}

// 删除项目
export async function deleteProject(projectId: number): Promise<void> {
  const sql = 'DELETE FROM blog_projects WHERE id = ?'
  await query(sql, [projectId])
}

// 修改项目
export async function updateProject(projectId: number, project: Project): Promise<void> {
  const sql = 'UPDATE blog_projects SET name = ?, description = ?, detail = ? WHERE id = ?'
  await query(sql, [project.name, project.description, project.detail, projectId])
}